﻿BEGIN TRY
	BEGIN TRAN

	MERGE [dbo].[DataCategory] AS TGT 
	USING
	(
		SELECT	1 as [DataCategoryId],
				NULL as [ParentDataCategoryId],
				'MS SQL Server' as [DataCategoryName],
				0 as [IsEntity],
				0 as [IsAttribute],
				'False' as [IsDataTypeRequiredExpression],
				NULL as [DataTypeRegexPattern],
				'False' as [IsDataDefinitionRequiredExpression],
				'Leave data type blank' as [DataTypeDescription],
				'Leave data definition blank' as [DataDefinitionDescription],
				1 as [Level]
		UNION ALL
		SELECT	2 as [DataCategoryId],
				1 as [ParentDataCategoryId],
				'Database' as [DataCategoryName],
				0 as [IsEntity],
				0 as [IsAttribute],
				'False' as [IsDataTypeRequiredExpression],
				NULL as [DataTypeRegexPattern],
				'False' as [IsDataDefinitionRequiredExpression],
				'Leave data type blank' as [DataTypeDescription],
				'Leave data definition blank' as [DataDefinitionDescription],
				2 as [Level]
		UNION ALL
		SELECT	3 as [DataCategoryId],
				2 as [ParentDataCategoryId],
				'Schema' as [DataCategoryName],
				0 as [IsEntity],
				0 as [IsAttribute],
				'False' as [IsDataTypeRequiredExpression],
				NULL as [DataTypeRegexPattern],
				'False' as [IsDataDefinitionRequiredExpression],
				'Leave data type blank' as [DataTypeDescription],
				'Leave data definition blank' as [DataDefinitionDescription],
				3 as [Level]
		UNION ALL
		SELECT	4 as [DataategoryId],
				3 as [ParentDataCategoryId],
				'Object' as [DataCategoryName],
				1 as [IsEntity],
				0 as [IsAttribute],
				'True' as [IsDataTypeRequiredExpression],
				'(?i:table|view|query)' as [DataTypeRegexPattern],
				'entity.DataType.ToUpper() == "QUERY"' as [IsDataDefinitionRequiredExpression],
				'Table, View or Query' as [DataTypeDescription],
				'If data type is Query write query definition here, otherwise leave it blank' as [DataDefinitionDescription],
				4 as [Level]
		UNION ALL
		SELECT	5 as [DataCategoryId],
				4 as [ParentDataCategoryId],
				'Column' as [DataCategoryName],
				0 as [IsEntity],
				1 as [IsAttribute],
				'True' as [IsDataTypeRequiredExpression],
				'(((?i:binary|char|datetime2|datetimeoffset|nchar|nvarchar|time|varbinary|varchar)[(]\d+[)])|((?i:nvarchar|varbinary|varchar)[(](?i:max)[)])|((decimal|numeric)[(]\d+[,]\d+[)])|(?i:bigint|bit|date|datetime|float|geography|geometry|hierarchyid|image|int|money|ntext|real|smalldatetime|smallint|smallmoney|sql_variant|text|timestamp|tinyint|uniqueidentifier|xml))' as [DataTypeRegexPattern],
				'False' as [IsDataDefinitionRequiredExpression],
				'Standard MS SQL data type with precision. For example, varchar(20)' as [DataTypeDescription],
				'Leave data definition blank' as [DataDefinitionDescription],
				5 as [Level]


		-- Microsoft Azure SQL DW
		UNION ALL
		SELECT	6 as [DataCategoryId],
				NULL as [ParentDataCategoryId],
				'Azure SQL Data Warehouse' as [DataCategoryName],
				0 as [IsEntity],
				0 as [IsAttribute],
				'False' as [IsDataTypeRequiredExpression],
				NULL as [DataTypeRegexPattern],
				'False' as [IsDataDefinitionRequiredExpression],
				'Leave data type blank' as [DataTypeDescription],
				'Leave data definition blank' as [DataDefinitionDescription],
				1 as [Level]
		UNION ALL
		SELECT	7 as [DataCategoryId],
				6 as [ParentDataCategoryId],
				'Database' as [DataCategoryName],
				0 as [IsEntity],
				0 as [IsAttribute],
				'False' as [IsDataTypeRequiredExpression],
				NULL as [DataTypeRegexPattern],
				'False' as [IsDataDefinitionRequiredExpression],
				'Leave data type blank' as [DataTypeDescription],
				'Leave data definition blank' as [DataDefinitionDescription],
				2 as [Level]
		UNION ALL
		SELECT	8 as [DataCategoryId],
				7 as [ParentDataCategoryId],
				'Schema' as [DataCategoryName],
				0 as [IsEntity],
				0 as [IsAttribute],
				'False' as [IsDataTypeRequiredExpression],
				NULL as [DataTypeRegexPattern],
				'False' as [IsDataDefinitionRequiredExpression],
				'Leave data type blank' as [DataTypeDescription],
				'Leave data definition blank' as [DataDefinitionDescription],
				3 as [Level]
		UNION ALL
		SELECT	9 as [DataategoryId],
				8 as [ParentDataCategoryId],
				'Object' as [DataCategoryName],
				1 as [IsEntity],
				0 as [IsAttribute],
				'True' as [IsDataTypeRequiredExpression],
				'(?i:table|view|query)' as [DataTypeRegexPattern],
				'entity.DataType.ToUpper() == "QUERY"' as [IsDataDefinitionRequiredExpression],
				'Table, View or Query' as [DataTypeDescription],
				'If data type is Query write query definition here, otherwise leave it blank' as [DataDefinitionDescription],
				4 as [Level]
		UNION ALL
		SELECT	10 as [DataCategoryId],
				9 as [ParentDataCategoryId],
				'Column' as [DataCategoryName],
				0 as [IsEntity],
				1 as [IsAttribute],
				'True' as [IsDataTypeRequiredExpression],
				'(((?i:binary|char|datetime2|datetimeoffset|nchar|nvarchar|time|varbinary|varchar)[(]\d+[)])|((?i:nvarchar|varbinary|varchar)[(](?i:max)[)])|((decimal|numeric)[(]\d+[,]\d+[)])|(?i:bigint|bit|date|datetime|float|geography|geometry|hierarchyid|image|int|money|ntext|real|smalldatetime|smallint|smallmoney|sql_variant|text|timestamp|tinyint|uniqueidentifier|xml))' as [DataTypeRegexPattern],
				'False' as [IsDataDefinitionRequiredExpression],
				'Standard MS SQL data type with precision. For example, varchar(20)' as [DataTypeDescription],
				'Leave data definition blank' as [DataDefinitionDescription],
				5 as [Level]
	) AS SRC (
		[DataCategoryId],
		[ParentDataCategoryId],
		[DataCategoryName],
		[IsEntity],
		[IsAttribute],
		[IsDataTypeRequiredExpression],
		[DataTypeRegexPattern],
		[IsDataDefinitionRequiredExpression],
		[DataTypeDescription],
		[DataDefinitionDescription],
		[Level]
	)
	ON TGT.[DataCategoryId] = SRC.[DataCategoryId]  

	WHEN MATCHED THEN   
	UPDATE SET
		[ParentDataCategoryId] = SRC.[ParentDataCategoryId],
		[DataCategoryName] = SRC.[DataCategoryName],
		[IsEntity] = SRC.[IsEntity],
		[IsAttribute] = SRC.[IsAttribute],
		[IsDataTypeRequiredExpression] = SRC.[IsDataTypeRequiredExpression],
		[DataTypeRegexPattern] = SRC.[DataTypeRegexPattern],
		[IsDataDefinitionRequiredExpression] = SRC.[IsDataDefinitionRequiredExpression],
		[DataTypeDescription] = SRC.[DataTypeDescription],
		[DataDefinitionDescription] = SRC.[DataDefinitionDescription],
		[Level] = SRC.[Level]
	WHEN NOT MATCHED BY TARGET THEN  
		INSERT (
			[DataCategoryId],
			[ParentDataCategoryId],
			[DataCategoryName],
			[IsEntity],
			[IsAttribute],
			[IsDataTypeRequiredExpression],
			[DataTypeRegexPattern],
			[IsDataDefinitionRequiredExpression],
			[DataTypeDescription],
			[DataDefinitionDescription],
			[Level]
		)
		VALUES (
			SRC.[DataCategoryId],
			SRC.[ParentDataCategoryId],
			SRC.[DataCategoryName],
			SRC.[IsEntity],
			SRC.[IsAttribute],
			SRC.[IsDataTypeRequiredExpression],
			SRC.[DataTypeRegexPattern],
			SRC.[IsDataDefinitionRequiredExpression],
			SRC.[DataTypeDescription],
			SRC.[DataDefinitionDescription],
			SRC.[Level]
		)

	WHEN NOT MATCHED BY SOURCE THEN  
		DELETE;

	COMMIT TRAN;
END TRY
BEGIN CATCH
	ROLLBACK TRAN;
END CATCH